/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Model;

/**
 *
 * @author QUYET
 */
public class CategoryAccessor extends Model {

    public List<Category> getRootCategories() {
        List<Category> list = new ArrayList<Category>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT categoryID, categoryName FROM tblCategory WHERE tblCategory.parent_id = 0";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Category category = new Category();
                category.setCategoryID(rs.getInt("categoryID"));
                category.setCategoryName(rs.getString("categoryName"));
                list.add(category);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return list;
        }
    }
    public List<Category> listAllCategories(){
        List<Category> list = new ArrayList<Category>();;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT categoryID, categoryName FROM tblCategory";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Category category = new Category();
                category.setCategoryID(rs.getInt("categoryID"));
                category.setCategoryName(rs.getString("categoryName"));
                list.add(category);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return list;
        }
    }
    public Category getCategoryByID(int catID){
        Category category = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT categoryID, categoryName FROM tblCategory Where categoryID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, catID);
            rs = ps.executeQuery();
            if(rs.next()) {
                category = new Category();
                category.setCategoryID(rs.getInt("categoryID"));
                category.setCategoryName(rs.getString("categoryName"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
            return category;
        } catch (Exception ex) {
            ex.printStackTrace();
            return category;
        }
    }
    public int deleteProductCategory(int catID){
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Delete from tblProductCategory Where categoryID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, catID);
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rowAffected;
        }
        
    }
    public int deleteCategory(int catID){
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Delete from tblCategory Where categoryID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, catID);
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rowAffected;
        }
        
    }
    public int updateCategory(int catID,String catName){
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update  tblCategory set categoryName = ? Where categoryID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, catName);
            ps.setInt(2, catID);
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rowAffected;
        }
        
    }
    public int addCategory(String catName){
        int rowAffected = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Insert into  tblCategory(categoryName,parent_id,depth) values(?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, catName);
            ps.setInt(2, 0);
            ps.setInt(3, 0);
            rowAffected = ps.executeUpdate();
            closeObject(ps);
            closeObject(conn);
            return rowAffected;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rowAffected;
        }
        
    }
}
